package Sport;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import players.BasketballPlayer;
import players.FootballPlayer;
import players.HockeyPlayer;
import players.Player;
import teams.*;

/*
 * To change this template, choose Tools | Templates and open the template in
 * the editor.
 */
/**
 *
 * @author denaas
 */
public class DBManager {

    private static java.sql.Connection conn;

    /**
     * Creates new form DataBase
     */
    public DBManager() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        Locale.setDefault(Locale.ENGLISH);
        String driver = "oracle.jdbc.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:oracle";
        String userName = "den";
        String password = "den";
        Class.forName(driver).newInstance();
        conn = DriverManager.getConnection(url, userName, password);
    }

    private void addFootballPlayer(FootballPlayer pl) throws SQLException {
        
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO FOOTBALLPLAYERS VALUES ("
                + "SEQPLAYER.CURRVAL,?,?,?,?,?,?,?)");
        stmt.setString(1, pl.getAttrValueToStr("position"));
        stmt.setInt(2, Integer.valueOf(pl.getAttrValueToStr("games")).intValue());
        stmt.setInt(3, Integer.valueOf(pl.getAttrValueToStr("goals")).intValue());
        stmt.setInt(4, Integer.valueOf(pl.getAttrValueToStr("assists")).intValue());
        stmt.setInt(5, Integer.valueOf(pl.getAttrValueToStr("yellowCards")).intValue());
        stmt.setInt(6, Integer.valueOf(pl.getAttrValueToStr("redCards")).intValue());
        stmt.setDouble(7, Double.valueOf(pl.getAttrValueToStr("minutes")).doubleValue());
        stmt.executeUpdate();
    }

    private void addBasketballPlayer(BasketballPlayer pl) throws SQLException {
        
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO BASKETBALLPLAYERS VALUES ("
                + "SEQPLAYER.CURRVAL,?,?,?,?,?,?,?,?,?,?)");
        stmt.setString(1, pl.getAttrValueToStr("position"));
        stmt.setInt(2, Integer.valueOf(pl.getAttrValueToStr("games")).intValue());
        stmt.setInt(3, Integer.valueOf(pl.getAttrValueToStr("points")).intValue());
        stmt.setInt(4, Integer.valueOf(pl.getAttrValueToStr("rebounds")).intValue());
        stmt.setInt(5, Integer.valueOf(pl.getAttrValueToStr("assists")).intValue());
        stmt.setInt(6, Integer.valueOf(pl.getAttrValueToStr("blocks")).intValue());
        stmt.setInt(7, Integer.valueOf(pl.getAttrValueToStr("steals")).intValue());
        stmt.setInt(8, Integer.valueOf(pl.getAttrValueToStr("turnovers")).intValue());
        stmt.setInt(9, Integer.valueOf(pl.getAttrValueToStr("fouls")).intValue());
        stmt.setDouble(10, Double.valueOf(pl.getAttrValueToStr("minutes")).doubleValue());
        stmt.executeUpdate();
    }

    private void addHockeyPlayer(HockeyPlayer pl) throws SQLException {
        
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO HOCKEYPLAYERS VALUES ("
                + "SEQPLAYER.CURRVAL,?,?,?,?,?,?,?,?)");
        stmt.setString(1, pl.getAttrValueToStr("position"));
        stmt.setInt(2, Integer.valueOf(pl.getAttrValueToStr("games")).intValue());
        stmt.setInt(3, Integer.valueOf(pl.getAttrValueToStr("goals")).intValue());
        stmt.setInt(4, Integer.valueOf(pl.getAttrValueToStr("assists")).intValue());
        stmt.setInt(5, Integer.valueOf(pl.getAttrValueToStr("blocks")).intValue());
        stmt.setInt(6, Integer.valueOf(pl.getAttrValueToStr("steals")).intValue());
        stmt.setDouble(7, Double.valueOf(pl.getAttrValueToStr("penaltyMinutes")).doubleValue());
        stmt.setDouble(8, Double.valueOf(pl.getAttrValueToStr("minutes")).doubleValue());
        stmt.executeUpdate();
    }

    public void addPlayer(Player pl) throws SQLException {
        
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO PLAYERS VALUES (SEQPLAYER.NEXTVAL,?,?,?,?,?,?,?)");
        stmt.setString(1, pl.getAttrValueToStr("firstName"));
        stmt.setString(2, pl.getAttrValueToStr("lastName"));
        stmt.setString(3, pl.getAttrValueToStr("country"));
        stmt.setInt(4, Integer.valueOf(pl.getAttrValueToStr("dateOfBirth")).intValue());
        stmt.setDouble(5, Double.valueOf(pl.getAttrValueToStr("height")).doubleValue());
        stmt.setDouble(6, Double.valueOf(pl.getAttrValueToStr("weight")).doubleValue());
        stmt.setInt(7, Integer.valueOf(pl.getAttrValueToStr("teamId")).intValue());
        stmt.executeUpdate();

        if (pl instanceof BasketballPlayer) {
            addBasketballPlayer((BasketballPlayer) pl);
        } else if (pl instanceof HockeyPlayer) {
            addHockeyPlayer((HockeyPlayer) pl);
        } else if (pl instanceof FootballPlayer) {
            addFootballPlayer((FootballPlayer) pl);
        }
    }

    private void editFootballPlayer(FootballPlayer pl) throws SQLException {
        Statement stmt = conn.createStatement();
        String query = "UPDATE FOOTBALLPLAYERS SET ";
        if (pl.getPosition() != null) {
            query += "POSITION='" + pl.getPosition() + "', ";
        }
        if (pl.getGames() != null) {
            query += "GAMES=" + pl.getGames() + ", ";
        }
        if (pl.getGoals() != null) {
            query += "GOALS=" + pl.getGoals() + ", ";
        }
        if (pl.getAssists() != null) {
            query += "ASSISTS=" + pl.getAssists() + ", ";
        }
        if (pl.getYellowCards() != null) {
            query += "YELLOWCARDS=" + pl.getYellowCards() + ", ";
        }
        if (pl.getYellowCards() != null) {
            query += "REDCARDS=" + pl.getYellowCards() + ", ";
        }
        if (pl.getYellowCards() != null) {
            query += "MINUTES=" + pl.getYellowCards() + ", ";
        }
        query = query.substring(0, query.length() - 2);
        query += " WHERE FOOTBALLPLAYERS.PLAYERID = " + pl.getAttrValueToStr("id");
        stmt.executeQuery(query);
    }

    private void editBasketballPlayer(BasketballPlayer pl) throws SQLException {
        Statement stmt = conn.createStatement();
        String query = "UPDATE BASKETBALLPLAYERS SET ";
        if (pl.getPosition() != null) {
            query += "POSITION='" + pl.getPosition() + "', ";
        }
        if (pl.getGames() != null) {
            query += "GAMES=" + pl.getGames() + ", ";
        }
        if (pl.getPoints() != null) {
            query += "POINTS=" + pl.getPoints() + ", ";
        }
        if (pl.getRebounds() != null) {
            query += "REBOUNDS=" + pl.getRebounds() + ", ";
        }
        if (pl.getAssists() != null) {
            query += "ASSISTS=" + pl.getAssists() + ", ";
        }
        if (pl.getBlocks() != null) {
            query += "BLOCKS=" + pl.getBlocks() + ", ";
        }
        if (pl.getSteals() != null) {
            query += "STEALS=" + pl.getSteals() + ", ";
        }
        if (pl.getTurnovers() != null) {
            query += "TURNOVERS=" + pl.getTurnovers() + ", ";
        }
        if (pl.getFouls() != null) {
            query += "FOULS=" + pl.getFouls() + ", ";
        }
        if (pl.getMinutes() != null) {
            query += "MINUTES=" + pl.getMinutes() + ", ";
        }
        query = query.substring(0, query.length() - 2);
        query += " WHERE BASKETBALLPLAYERS.PLAYERID = " + pl.getAttrValueToStr("id");
        stmt.executeQuery(query);
    }

    private void editHockeyPlayer(HockeyPlayer pl) throws SQLException {
        Statement stmt = conn.createStatement();
        String query = "UPDATE HOCKEYPLAYERS SET ";
        if (pl.getPosition() != null) {
            query += "POSITION='" + pl.getPosition() + "', ";
        }
        if (pl.getGames() != null) {
            query += "GAMES=" + pl.getGames() + ", ";
        }
        if (pl.getGoals() != null) {
            query += "GOALS=" + pl.getGoals() + ", ";
        }
        if (pl.getAssists() != null) {
            query += "ASSISTS=" + pl.getAssists() + ", ";
        }
        if (pl.getBlocks() != null) {
            query += "BLOCKS=" + pl.getBlocks() + ", ";
        }
        if (pl.getSteals() != null) {
            query += "STEALS=" + pl.getSteals() + ", ";
        }
        if (pl.getPenaltyMinutes() != null) {
            query += "PENALTYMINUTES=" + pl.getPenaltyMinutes() + ", ";
        }
        if (pl.getMinutes() != null) {
            query += "MINUTES=" + pl.getMinutes() + ", ";
        }
        query = query.substring(0, query.length() - 2);
        query += " WHERE HOCKEYPLAYERS.PLAYERID = " + pl.getAttrValueToStr("id");
        stmt.executeQuery(query);
    }

    public void editPlayer(Player pl) throws SQLException {
        Statement stmt = conn.createStatement();
        String query = "UPDATE PLAYERS SET ";
        if (pl.getFirstName() != null) {
            query += "FIRSTNAME='" + pl.getFirstName() + "', ";
        }
        if (pl.getLastName() != null) {
            query += "LASTNAME='" + pl.getLastName() + "', ";
        }
        if (pl.getCountry() != null) {
            query += "COUNTRY='" + pl.getCountry() + "', ";
        }
        if (pl.getDateOfBirth() != null) {
            query += "DATEOFBIRTH=" + pl.getDateOfBirth() + ", ";
        }
        if (pl.getHeight() != null) {
            query += "HEIGHT=" + pl.getHeight() + ", ";
        }
        if (pl.getWeight() != null) {
            query += "WEIGHT=" + pl.getWeight() + ", ";
        }
        if (pl.getTeamId() != null) {
            query += "TEAM=" + pl.getTeamId() + ", ";
        }
        query = query.substring(0, query.length() - 2);
        query += " WHERE PLAYERS.PLAYERID = " + pl.getAttrValueToStr("id");
        stmt.executeQuery(query);

        if (pl instanceof BasketballPlayer) {
            editBasketballPlayer((BasketballPlayer) pl);
        } else if (pl instanceof HockeyPlayer) {
            editHockeyPlayer((HockeyPlayer) pl);
        } else if (pl instanceof FootballPlayer) {
            editFootballPlayer((FootballPlayer) pl);
        }
    }

    public void removePlayer(int playerId) throws SQLException {
        PreparedStatement stmt = conn.prepareStatement("DELETE FROM BASKETBALLPLAYERS WHERE playerid = ?");
        stmt.setInt(1, playerId);
        stmt.executeUpdate();
        stmt = conn.prepareStatement("DELETE FROM HOCKEYPLAYERS WHERE playerid = ?");
        stmt.setInt(1, playerId);
        stmt.executeUpdate();
        stmt = conn.prepareStatement("DELETE FROM FOOTBALLPLAYERS WHERE playerid = ?");
        stmt.setInt(1, playerId);
        stmt.executeUpdate();
        stmt = conn.prepareStatement("DELETE FROM players WHERE playerid = ?");
        stmt.setInt(1, playerId);
        stmt.executeUpdate();
        
    }

    private void addFootballTeam(FootballTeam tm) throws SQLException {
        
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO FOOTBALLTEAMS VALUES (SEQTEAM.CURRVAL,?,?,?,?,?,?)");
        stmt.setInt(1, Integer.valueOf(tm.getAttrValueToStr("wins")).intValue());
        stmt.setInt(2, Integer.valueOf(tm.getAttrValueToStr("draws")).intValue());
        stmt.setInt(3, Integer.valueOf(tm.getAttrValueToStr("losses")).intValue());
        stmt.setInt(4, Integer.valueOf(tm.getAttrValueToStr("points")).intValue());
        stmt.setInt(5, Integer.valueOf(tm.getAttrValueToStr("goalsScored")).intValue());
        stmt.setInt(6, Integer.valueOf(tm.getAttrValueToStr("goalsAgainst")).intValue());
        
        stmt.executeUpdate();
    }

    private void addBasketballTeam(BasketballTeam tm) throws SQLException {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO BASKETBALLTEAMS VALUES (SEQTEAM.CURRVAL,?,?)");
        stmt.setInt(1, Integer.valueOf(tm.getAttrValueToStr("wins")).intValue());
        stmt.setInt(2, Integer.valueOf(tm.getAttrValueToStr("losses")).intValue());
                
        stmt.executeUpdate();
    }

    private void addHockeyTeam(HockeyTeam tm) throws SQLException {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO HOCKEYTEAMS VALUES (SEQTEAM.CURRVAL,?,?,?,?,?,?,?)");
        stmt.setInt(1, Integer.valueOf(tm.getAttrValueToStr("wins")).intValue());
        stmt.setInt(2, Integer.valueOf(tm.getAttrValueToStr("winsOT")).intValue());
        stmt.setInt(3, Integer.valueOf(tm.getAttrValueToStr("lossesOT")).intValue());
        stmt.setInt(4, Integer.valueOf(tm.getAttrValueToStr("losses")).intValue());
        stmt.setInt(5, Integer.valueOf(tm.getAttrValueToStr("points")).intValue());
        stmt.setInt(6, Integer.valueOf(tm.getAttrValueToStr("goalsScored")).intValue());
        stmt.setInt(7, Integer.valueOf(tm.getAttrValueToStr("goalsAgainst")).intValue());
        
        stmt.executeUpdate(); 
    }

    public void addTeam(Team tm) throws SQLException {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO TEAMS VALUES (SEQTEAM.NEXTVAL,?,?,?,NULL,NULL,NULL)");
        stmt.setString(1, tm.getAttrValueToStr("name"));
        stmt.setString(2, tm.getAttrValueToStr("city"));
        stmt.setInt(3, Integer.valueOf(tm.getAttrValueToStr("sportId")).intValue());        
        
        stmt.executeUpdate();        

        if (tm instanceof BasketballTeam) {
            addBasketballTeam((BasketballTeam) tm);
        } else if (tm instanceof HockeyTeam) {
            addHockeyTeam((HockeyTeam) tm);
        } else if (tm instanceof FootballTeam) {
            addFootballTeam((FootballTeam) tm);
        }
    }

    
    //todo prepared statements for all editing
    private void editFootballTeam(FootballTeam tm) throws SQLException {
        Statement stmt = conn.createStatement();
        String query = "UPDATE FOOTBALLTEAMS SET ";
        if (tm.getWins() != null) {
            query += "WINS=" + tm.getWins() + ", ";
        }
        if (tm.getDraws() != null) {
            query += "DRAWS=" + tm.getDraws() + ", ";
        }
        if (tm.getLosses() != null) {
            query += "LOSSES=" + tm.getLosses() + ", ";
        }
        if (tm.getPoints() != null) {
            query += "POINTS=" + tm.getPoints() + ", ";
        }
        if (tm.getGoalsScored() != null) {
            query += "GOALSSCORED=" + tm.getGoalsScored() + ", ";
        }
        if (tm.getGoalsAgainst() != null) {
            query += "GOALSAGAINST=" + tm.getGoalsAgainst() + ", ";
        }
        query = query.substring(0, query.length() - 2);
        query += " WHERE FOOTBALLTEAMS.TEAMID = " + tm.getAttrValueToStr("id");
        stmt.executeQuery(query);
    }

    private void editBasketballTeam(BasketballTeam tm) throws SQLException {
        Statement stmt = conn.createStatement();
        String query = "UPDATE BASKETBALLTEAMS SET ";
        if (tm.getWins() != null) {
            query += "WINS=" + tm.getWins() + ", ";
        }
        if (tm.getLosses() != null) {
            query += "LOSSES=" + tm.getLosses() + ", ";
        }
        query = query.substring(0, query.length() - 2);
        query += " WHERE BASKETBALLTEAMS.TEAMID = " + tm.getAttrValueToStr("id");
        stmt.executeQuery(query);
    }

    private void editHockeyTeam(HockeyTeam tm) throws SQLException {
        Statement stmt = conn.createStatement();
        String query = "UPDATE HOCKEYTEAMS SET ";
        if (tm.getWins() != null) {
            query += "WINS=" + tm.getWins() + ", ";
        }
        if (tm.getWinsOT() != null) {
            query += "WINSOT=" + tm.getWinsOT() + ", ";
        }
        if (tm.getLossesOT() != null) {
            query += "LOSSESOT=" + tm.getLossesOT() + ", ";
        }
        if (tm.getLosses() != null) {
            query += "LOSSES=" + tm.getLosses() + ", ";
        }
        if (tm.getPoints() != null) {
            query += "POINTS=" + tm.getPoints() + ", ";
        }
        if (tm.getGoalsScored() != null) {
            query += "GOALSSCORED=" + tm.getGoalsScored() + ", ";
        }
        if (tm.getGoalsAgainst() != null) {
            query += "GOALSAGAINST=" + tm.getGoalsAgainst() + ", ";
        }
        query = query.substring(0, query.length() - 2);
        query += " WHERE HOCKEYTEAMS.TEAMID = " + tm.getAttrValueToStr("id");
        stmt.executeQuery(query);
    }

    public void editTeam(Team tm) throws SQLException {
        Statement stmt = conn.createStatement();
        String query = "UPDATE TEAMS SET ";
        if (tm.getName() != null) {
            query += "TEAMNAME='" + tm.getName() + "', ";
        }
        if (tm.getCity() != null) {
            query += "CITY='" + tm.getCity() + "', ";
        }
        if (tm.getSportId() != null) {
            query += "SPORT=" + tm.getSportId() + ", ";
        }
        query = query.substring(0, query.length() - 2);
        query += " WHERE TEAMS.TEAMID = " + tm.getAttrValueToStr("id");
        stmt.executeQuery(query);

        if (tm instanceof BasketballTeam) {
            editBasketballTeam((BasketballTeam) tm);
        } else if (tm instanceof HockeyTeam) {
            editHockeyTeam((HockeyTeam) tm);
        } else if (tm instanceof FootballTeam) {
            editFootballTeam((FootballTeam) tm);
        }
    }

        
    
    
    
    public void removeTeam(int teamId) throws SQLException {
        PreparedStatement stmt = conn.prepareStatement("DELETE FROM BASKETBALLTEAMS WHERE teamid = ?");
        stmt.setInt(1, teamId);
        stmt.executeUpdate();
        stmt = conn.prepareStatement("DELETE FROM HOCKEYTEAMS WHERE teamid = ?");
        stmt.setInt(1, teamId);
        stmt.executeUpdate();
        stmt = conn.prepareStatement("DELETE FROM FOOTBALLTEAMS WHERE teamid = ?");
        stmt.setInt(1, teamId);
        stmt.executeUpdate();
        stmt = conn.prepareStatement("DELETE FROM teams WHERE teamid = ?");
        stmt.setInt(1, teamId);
        stmt.executeUpdate();
        
    }

    private List selectBasketballPlayers(Integer playerId, Integer teamId) throws SQLException {
        PreparedStatement stmt = null;
        ResultSet rs = null;

        if (playerId != null) {
            stmt = conn.prepareStatement("select * from BASKETBALLPLAYERS, PLAYERS where BASKETBALLPLAYERS.PLAYERID = PLAYERS.PLAYERID and PLAYERS.PLAYERID = ?");
            stmt.setInt(1, playerId.intValue());
        } else if (teamId != null) {
            stmt = conn.prepareStatement("select * from BASKETBALLPLAYERS, PLAYERS where BASKETBALLPLAYERS.PLAYERID = PLAYERS.PLAYERID and team = ?");
            stmt.setInt(1, teamId.intValue());
        } else {
            stmt = conn.prepareStatement("select * from BASKETBALLPLAYERS, PLAYERS where BASKETBALLPLAYERS.PLAYERID = PLAYERS.PLAYERID");
        }

        rs = stmt.executeQuery();

        List result = new LinkedList();
        while (rs.next()) {
            BasketballPlayer pl = new BasketballPlayer();
            pl.setPosition(rs.getString("POSITION"));
            pl.setGames(new Integer(rs.getInt("GAMES")));
            pl.setPoints(new Integer(rs.getInt("POINTS")));
            pl.setRebounds(new Integer(rs.getInt("REBOUNDS")));
            pl.setAssists(new Integer(rs.getInt("ASSISTS")));
            pl.setBlocks(new Integer(rs.getInt("BLOCKS")));
            pl.setSteals(new Integer(rs.getInt("STEALS")));
            pl.setTurnovers(new Integer(rs.getInt("TURNOVERS")));
            pl.setFouls(new Integer(rs.getInt("FOULS")));
            pl.setMinutes(new Double(rs.getDouble("MINUTES")));

            pl.setId(new Integer(rs.getInt("PLAYERID")));
            pl.setFirstName(rs.getString("FIRSTNAME"));
            pl.setLastName(rs.getString("LASTNAME"));
            pl.setCountry(rs.getString("COUNTRY"));
            pl.setDateOfBirth(new Integer(rs.getInt("DATEOFBIRTH")));
            pl.setHeight(new Double(rs.getDouble("HEIGHT")));
            pl.setWeight(new Double(rs.getDouble("WEIGHT")));
            pl.setTeamId(new Integer(rs.getInt("TEAM")));
            result.add(pl);
        }

        return result;
    }

    private List selectFootballPlayers(Integer playerId, Integer teamId) throws SQLException {

        PreparedStatement stmt = null;
        ResultSet rs = null;

        if (playerId != null) {
            stmt = conn.prepareStatement("select * from FOOTBALLPLAYERS, PLAYERS where FOOTBALLPLAYERS.PLAYERID = PLAYERS.PLAYERID and PLAYERS.PLAYERID = ?");
            stmt.setInt(1, playerId.intValue());
        } else if (teamId != null) {
            stmt = conn.prepareStatement("select * from FOOTBALLPLAYERS, PLAYERS where FOOTBALLPLAYERS.PLAYERID = PLAYERS.PLAYERID and team = ?");
            stmt.setInt(1, teamId.intValue());
        } else {
            stmt = conn.prepareStatement("select * from FOOTBALLPLAYERS, PLAYERS where FOOTBALLPLAYERS.PLAYERID = PLAYERS.PLAYERID");
        }

        rs = stmt.executeQuery();

        List result = new LinkedList();
        while (rs.next()) {
            FootballPlayer pl = new FootballPlayer();
            pl.setPosition(rs.getString("POSITION"));
            pl.setGames(new Integer(rs.getInt("GAMES")));
            pl.setGoals(new Integer(rs.getInt("GOALS")));
            pl.setAssists(new Integer(rs.getInt("ASSISTS")));
            pl.setYellowCards(new Integer(rs.getInt("YELLOWCARDS")));
            pl.setRedCards(new Integer(rs.getInt("REDCARDS")));
            pl.setMinutes(new Double(rs.getDouble("MINUTES")));

            pl.setId(new Integer(rs.getInt("PLAYERID")));
            pl.setFirstName(rs.getString("FIRSTNAME"));
            pl.setLastName(rs.getString("LASTNAME"));
            pl.setCountry(rs.getString("COUNTRY"));
            pl.setDateOfBirth(new Integer(rs.getInt("DATEOFBIRTH")));
            pl.setHeight(new Double(rs.getDouble("HEIGHT")));
            pl.setWeight(new Double(rs.getDouble("WEIGHT")));
            pl.setTeamId(new Integer(rs.getInt("TEAM")));
            result.add(pl);
        }

        return result;
    }

    private List selectHockeyPlayers(Integer playerId, Integer teamId) throws SQLException {
        PreparedStatement stmt = null;
        ResultSet rs = null;

        if (playerId != null) {
            stmt = conn.prepareStatement("select * from HOCKEYPLAYERS, PLAYERS where HOCKEYPLAYERS.PLAYERID = PLAYERS.PLAYERID and PLAYERS.PLAYERID = ?");
            stmt.setInt(1, playerId.intValue());
        } else if (teamId != null) {
            stmt = conn.prepareStatement("select * from HOCKEYPLAYERS, PLAYERS where HOCKEYPLAYERS.PLAYERID = PLAYERS.PLAYERID and team = ?");
            stmt.setInt(1, teamId.intValue());
        } else {
            stmt = conn.prepareStatement("select * from HOCKEYPLAYERS, PLAYERS where HOCKEYPLAYERS.PLAYERID = PLAYERS.PLAYERID");
        }

        rs = stmt.executeQuery();


        List result = new LinkedList();
        while (rs.next()) {
            HockeyPlayer pl = new HockeyPlayer();
            pl.setPosition(rs.getString("POSITION"));
            pl.setGames(new Integer(rs.getInt("GAMES")));
            pl.setGoals(new Integer(rs.getInt("GOALS")));
            pl.setAssists(new Integer(rs.getInt("ASSISTS")));
            pl.setBlocks(new Integer(rs.getInt("BLOCKS")));
            pl.setSteals(new Integer(rs.getInt("STEALS")));
            pl.setPenaltyMinutes(new Double(rs.getDouble("PENALTYMINUTES")));
            pl.setMinutes(new Double(rs.getDouble("MINUTES")));

            pl.setId(new Integer(rs.getInt("PLAYERID")));
            pl.setFirstName(rs.getString("FIRSTNAME"));
            pl.setLastName(rs.getString("LASTNAME"));
            pl.setCountry(rs.getString("COUNTRY"));
            pl.setDateOfBirth(new Integer(rs.getInt("DATEOFBIRTH")));
            pl.setHeight(new Double(rs.getDouble("HEIGHT")));
            pl.setWeight(new Double(rs.getDouble("WEIGHT")));
            pl.setTeamId(new Integer(rs.getInt("TEAM")));
            result.add(pl);
        }

        return result;
    }

    public List selectPlayers(Integer playerId, Integer teamId, Integer sportId) throws SQLException, SQLNotFoundException {

        switch (sportId.intValue()) {
            case 1:
                return selectFootballPlayers(playerId, teamId);
            case 2:
                return selectBasketballPlayers(playerId, teamId);
            case 3:
                return selectHockeyPlayers(playerId, teamId);
            default:
                throw new SQLNotFoundException();
        }
    }

    private List selectBasketballTeams(Integer teamId) throws SQLException {

        PreparedStatement stmt = null;
        ResultSet rs = null;


        if (teamId != null) {
            stmt = conn.prepareStatement("select * from BASKETBALLTEAMS, TEAMS where BASKETBALLTEAMS.TEAMID = TEAMS.TEAMID and TEAMS.TEAMID = ?");
            stmt.setInt(1, teamId.intValue());
        } else {
            stmt = conn.prepareStatement("select * from BASKETBALLTEAMS, TEAMS where BASKETBALLTEAMS.TEAMID = TEAMS.TEAMID");
        }

        rs = stmt.executeQuery();

        List result = new LinkedList();
        while (rs.next()) {
            BasketballTeam tm = new BasketballTeam();
            tm.setId(new Integer(rs.getInt("TEAMID")));
            tm.setName(rs.getString("TEAMNAME"));
            tm.setCity(rs.getString("CITY"));
            tm.setWins(new Integer(rs.getInt("WINS")));
            tm.setLosses(new Integer(rs.getInt("LOSSES")));

            result.add(tm);
        }

        return result;
    }

    private List selectFootballTeams(Integer teamId) throws SQLException {
        PreparedStatement stmt = null;
        ResultSet rs = null;

        if (teamId != null) {
            stmt = conn.prepareStatement("select * from FOOTBALLTEAMS, TEAMS where FOOTBALLTEAMS.TEAMID = TEAMS.TEAMID and TEAMS.TEAMID = ?");
            stmt.setInt(1, teamId.intValue());
        } else {
            stmt = conn.prepareStatement("select * from FOOTBALLTEAMS, TEAMS where FOOTBALLTEAMS.TEAMID = TEAMS.TEAMID");
        }

        rs = stmt.executeQuery();

        List result = new LinkedList();
        while (rs.next()) {
            FootballTeam tm = new FootballTeam();
            tm.setId(new Integer(rs.getInt("TEAMID")));
            tm.setName(rs.getString("TEAMNAME"));
            tm.setCity(rs.getString("CITY"));
            tm.setWins(new Integer(rs.getInt("WINS")));
            tm.setLosses(new Integer(rs.getInt("LOSSES")));

            tm.setDraws(new Integer(rs.getInt("DRAWS")));
            tm.setPoints(new Integer(rs.getInt("POINTS")));
            tm.setGoalsScored(new Integer(rs.getInt("GOALSSCORED")));
            tm.setGoalsAgainst(new Integer(rs.getInt("GOALSAGAINST")));

            result.add(tm);
        }

        return result;
    }

    private List selectHockeyTeams(Integer teamId) throws SQLException {
        PreparedStatement stmt = null;
        ResultSet rs = null;

        if (teamId != null) {
            stmt = conn.prepareStatement("select * from HOCKEYTEAMS, TEAMS where HOCKEYTEAMS.TEAMID = TEAMS.TEAMID and TEAMS.TEAMID = ?");
            stmt.setInt(1, teamId.intValue());
        } else {
            stmt = conn.prepareStatement("select * from HOCKEYTEAMS, TEAMS where HOCKEYTEAMS.TEAMID = TEAMS.TEAMID");
        }

        rs = stmt.executeQuery();

        List result = new LinkedList();
        while (rs.next()) {
            HockeyTeam tm = new HockeyTeam();
            tm.setId(new Integer(rs.getInt("TEAMID")));
            tm.setName(rs.getString("TEAMNAME"));
            tm.setCity(rs.getString("CITY"));
            tm.setWins(new Integer(rs.getInt("WINS")));
            tm.setLosses(new Integer(rs.getInt("LOSSES")));

            tm.setWinsOT(new Integer(rs.getInt("WINSOT")));
            tm.setLossesOT(new Integer(rs.getInt("LOSSESOT")));
            tm.setPoints(new Integer(rs.getInt("POINTS")));
            tm.setGoalsScored(new Integer(rs.getInt("GOALSSCORED")));
            tm.setGoalsAgainst(new Integer(rs.getInt("GOALSAGAINST")));

            result.add(tm);
        }

        return result;
    }

    public List selectTeams(Integer teamId, Integer sportId) throws SQLNotFoundException, SQLException {
        switch (sportId.intValue()) {
            case 1:
                return selectFootballTeams(teamId);
            case 2:
                return selectBasketballTeams(teamId);
            case 3:
                return selectHockeyTeams(teamId);
            default:
                throw new SQLNotFoundException();
        }
    }

    
    
    
    
    
    //todo prepared statements for all queries
    private List selectBasketballPlayersQuery(BasketballPlayer plfrom, BasketballPlayer plto) throws SQLException {
        String query = "select * from BASKETBALLPLAYERS, PLAYERS where BASKETBALLPLAYERS.PLAYERID = PLAYERS.PLAYERID ";     
        
        List attrs1 = Reflectable.getAttrsNamesFromClass(BasketballPlayer.class);
        for (int i = 0; i < attrs1.size(); ++i) {
            String attr = (String) attrs1.get(i);
            if (plfrom.getAttrValueType(attr).equals(String.class)) {
                if (plfrom.getAttrValue(attr) != null) {
                    query += " and BASKETBALLPLAYERS." + attr.toUpperCase() + " = '" + plfrom.getAttrValueToStr(attr) + "'";
                }
            } else {
                if (plfrom.getAttrValue(attr) != null) {
                    query += " and BASKETBALLPLAYERS." + attr.toUpperCase() + " >= " + plfrom.getAttrValueToStr(attr);
                }
                if (plto.getAttrValue(attr) != null) {
                    query += " and BASKETBALLPLAYERS." + attr.toUpperCase() + " <= " + plto.getAttrValueToStr(attr);
                }
            }
        }
        
        List attrs2 = Reflectable.getAttrsNamesFromClass(Player.class);
        for (int i = 0; i < attrs2.size(); ++i) {
            String attr = (String) attrs2.get(i);
            if (plfrom.getAttrValueType(attr).equals(String.class)) {
                if (plfrom.getAttrValue(attr) != null) {
                    query += " and PLAYERS." + attr.toUpperCase() + " = '" + plfrom.getAttrValueToStr(attr) + "'";
                }
            } else {
                if (plfrom.getAttrValue(attr) != null) {
                    query += " and PLAYERS." + attr.toUpperCase() + " >= " + plfrom.getAttrValueToStr(attr);
                }
                if (plto.getAttrValue(attr) != null) {
                    query += " and PLAYERS." + attr.toUpperCase() + " <= " + plto.getAttrValueToStr(attr);
                }
            }
        }

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        List result = new LinkedList();
        while (rs.next()) {
            BasketballPlayer pl = new BasketballPlayer();
            pl.setPosition(rs.getString("POSITION"));
            pl.setGames(new Integer(rs.getInt("GAMES")));
            pl.setPoints(new Integer(rs.getInt("POINTS")));
            pl.setRebounds(new Integer(rs.getInt("REBOUNDS")));
            pl.setAssists(new Integer(rs.getInt("ASSISTS")));
            pl.setBlocks(new Integer(rs.getInt("BLOCKS")));
            pl.setSteals(new Integer(rs.getInt("STEALS")));
            pl.setTurnovers(new Integer(rs.getInt("TURNOVERS")));
            pl.setFouls(new Integer(rs.getInt("FOULS")));
            pl.setMinutes(new Double(rs.getDouble("MINUTES")));

            pl.setId(new Integer(rs.getInt("PLAYERID")));
            pl.setFirstName(rs.getString("FIRSTNAME"));
            pl.setLastName(rs.getString("LASTNAME"));
            pl.setCountry(rs.getString("COUNTRY"));
            pl.setDateOfBirth(new Integer(rs.getInt("DATEOFBIRTH")));
            pl.setHeight(new Double(rs.getDouble("HEIGHT")));
            pl.setWeight(new Double(rs.getDouble("WEIGHT")));
            pl.setTeamId(new Integer(rs.getInt("TEAM")));
            result.add(pl);
        }

        return result;
    }

    private List selectFootballPlayersQuery(FootballPlayer plfrom, FootballPlayer plto) throws SQLException {
        String query = "select * from FOOTBALLPLAYERS, PLAYERS where FOOTBALLPLAYERS.PLAYERID = PLAYERS.PLAYERID ";

        List attrs1 = Reflectable.getAttrsNamesFromClass(FootballPlayer.class);
        for (int i = 0; i < attrs1.size(); ++i) {
            String attr = (String) attrs1.get(i);
            if (plfrom.getAttrValueType(attr).equals(String.class)) {
                if (plfrom.getAttrValue(attr) != null) {
                    query += " and FOOTBALLPLAYERS." + attr.toUpperCase() + " = '" + plfrom.getAttrValueToStr(attr) + "'";
                }
            } else {
                if (plfrom.getAttrValue(attr) != null) {
                    query += " and FOOTBALLPLAYERS." + attr.toUpperCase() + " >= " + plfrom.getAttrValueToStr(attr);
                }
                if (plto.getAttrValue(attr) != null) {
                    query += " and FOOTBALLPLAYERS." + attr.toUpperCase() + " <= " + plto.getAttrValueToStr(attr);
                }
            }
        }
        
        List attrs2 = Reflectable.getAttrsNamesFromClass(Player.class);
        for (int i = 0; i < attrs2.size(); ++i) {
            String attr = (String) attrs2.get(i);
            if (plfrom.getAttrValueType(attr).equals(String.class)) {
                if (plfrom.getAttrValue(attr) != null) {
                    query += " and PLAYERS." + attr.toUpperCase() + " = '" + plfrom.getAttrValueToStr(attr) + "'";
                }
            } else {
                if (plfrom.getAttrValue(attr) != null) {
                    query += " and PLAYERS." + attr.toUpperCase() + " >= " + plfrom.getAttrValueToStr(attr);
                }
                if (plto.getAttrValue(attr) != null) {
                    query += " and PLAYERS." + attr.toUpperCase() + " <= " + plto.getAttrValueToStr(attr);
                }
            }
        }
        
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);


        List result = new LinkedList();
        while (rs.next()) {
            FootballPlayer pl = new FootballPlayer();
            pl.setPosition(rs.getString("POSITION"));
            pl.setGames(new Integer(rs.getInt("GAMES")));
            pl.setGoals(new Integer(rs.getInt("GOALS")));
            pl.setAssists(new Integer(rs.getInt("ASSISTS")));
            pl.setYellowCards(new Integer(rs.getInt("YELLOWCARDS")));
            pl.setRedCards(new Integer(rs.getInt("REDCARDS")));
            pl.setMinutes(new Double(rs.getDouble("MINUTES")));

            pl.setId(new Integer(rs.getInt("PLAYERID")));
            pl.setFirstName(rs.getString("FIRSTNAME"));
            pl.setLastName(rs.getString("LASTNAME"));
            pl.setCountry(rs.getString("COUNTRY"));
            pl.setDateOfBirth(new Integer(rs.getInt("DATEOFBIRTH")));
            pl.setHeight(new Double(rs.getDouble("HEIGHT")));
            pl.setWeight(new Double(rs.getDouble("WEIGHT")));
            pl.setTeamId(new Integer(rs.getInt("TEAM")));
            result.add(pl);
        }

        return result;
    }

    private List selectHockeyPlayersQuery(HockeyPlayer plfrom, HockeyPlayer plto) throws SQLException {
        String query = "select * from HOCKEYPLAYERS, PLAYERS where HOCKEYPLAYERS.PLAYERID = PLAYERS.PLAYERID ";

        List attrs1 = Reflectable.getAttrsNamesFromClass(HockeyPlayer.class);
        for (int i = 0; i < attrs1.size(); ++i) {
            String attr = (String) attrs1.get(i);
            if (plfrom.getAttrValueType(attr).equals(String.class)) {
                if (plfrom.getAttrValue(attr) != null) {
                    query += " and HOCKEYPLAYERS." + attr.toUpperCase() + " = '" + plfrom.getAttrValueToStr(attr) + "'";
                }
            } else {
                if (plfrom.getAttrValue(attr) != null) {
                    query += " and HOCKEYPLAYERS." + attr.toUpperCase() + " >= " + plfrom.getAttrValueToStr(attr);
                }
                if (plto.getAttrValue(attr) != null) {
                    query += " and HOCKEYPLAYERS." + attr.toUpperCase() + " <= " + plto.getAttrValueToStr(attr);
                }
            }
        }
        
        List attrs2 = Reflectable.getAttrsNamesFromClass(Player.class);
        for (int i = 0; i < attrs2.size(); ++i) {
            String attr = (String) attrs2.get(i);
            if (plfrom.getAttrValueType(attr).equals(String.class)) {
                if (plfrom.getAttrValue(attr) != null) {
                    query += " and PLAYERS." + attr.toUpperCase() + " = '" + plfrom.getAttrValueToStr(attr) + "'";
                }
            } else {
                if (plfrom.getAttrValue(attr) != null) {
                    query += " and PLAYERS." + attr.toUpperCase() + " >= " + plfrom.getAttrValueToStr(attr);
                }
                if (plto.getAttrValue(attr) != null) {
                    query += " and PLAYERS." + attr.toUpperCase() + " <= " + plto.getAttrValueToStr(attr);
                }
            }
        }
        
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);


        List result = new LinkedList();
        while (rs.next()) {
            HockeyPlayer pl = new HockeyPlayer();
            pl.setPosition(rs.getString("POSITION"));
            pl.setGames(new Integer(rs.getInt("GAMES")));
            pl.setGoals(new Integer(rs.getInt("GOALS")));
            pl.setAssists(new Integer(rs.getInt("ASSISTS")));
            pl.setBlocks(new Integer(rs.getInt("BLOCKS")));
            pl.setSteals(new Integer(rs.getInt("STEALS")));
            pl.setPenaltyMinutes(new Double(rs.getDouble("PENALTYMINUTES")));
            pl.setMinutes(new Double(rs.getDouble("MINUTES")));

            pl.setId(new Integer(rs.getInt("PLAYERID")));
            pl.setFirstName(rs.getString("FIRSTNAME"));
            pl.setLastName(rs.getString("LASTNAME"));
            pl.setCountry(rs.getString("COUNTRY"));
            pl.setDateOfBirth(new Integer(rs.getInt("DATEOFBIRTH")));
            pl.setHeight(new Double(rs.getDouble("HEIGHT")));
            pl.setWeight(new Double(rs.getDouble("WEIGHT")));
            pl.setTeamId(new Integer(rs.getInt("TEAM")));
            result.add(pl);
        }

        return result;
    }

    public List selectPlayersQuery(Player plfrom, Player plto, Integer sportId) throws SQLException, SQLNotFoundException {
        switch (sportId.intValue()) {
            case 1:
                return selectFootballPlayersQuery((FootballPlayer) plfrom, (FootballPlayer) plto);
            case 2:
                return selectBasketballPlayersQuery((BasketballPlayer) plfrom, (BasketballPlayer) plto);
            case 3:
                return selectHockeyPlayersQuery((HockeyPlayer) plfrom, (HockeyPlayer) plto);
            default:
                throw new SQLNotFoundException();
        }
    }
}